 package srdb.migration.dbhandler.enterprisedb;
 
 import java.util.Hashtable;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import srdb.migration.MTKException;
import srdb.migration.MigrationToolkit;
import srdb.migration.Utility;
import srdb.migration.common.MTKConstants;
import srdb.migration.common.MTKTransformDDL;
import srdb.migration.dbhandler.redwood.MetaData;
import srdb.migration.schema.metadata.Column;
import srdb.migration.schema.metadata.Constraint;
import srdb.migration.schema.metadata.DataType;
import srdb.migration.schema.metadata.Domain;
import srdb.migration.schema.metadata.Function;
import srdb.migration.schema.metadata.Grant;
import srdb.migration.schema.metadata.Group;
import srdb.migration.schema.metadata.Index;
import srdb.migration.schema.metadata.Package;
import srdb.migration.schema.metadata.Procedure;
import srdb.migration.schema.metadata.Role;
import srdb.migration.schema.metadata.Rule;
import srdb.migration.schema.metadata.Synonym;
import srdb.migration.schema.metadata.Table;
import srdb.migration.schema.metadata.Trigger;
import srdb.migration.schema.metadata.User;

 
 public class RedwoodTransformDDL extends MTKTransformDDL
 {
   public String getCreateScript(Table table)
     throws MTKException
   {
     String sql = super.getCreateScript(table);
     sql = sql + MetaData.SQL_STATEMENT_DELIMITER;
 
     sql = sql + getColumnsComments(table);
     return sql;
   }
 
   protected String getColumnLine(Column column) {
     String str = "";
     String colName = column.getName();
 
     if ((!column.isCaseSensitive()) && (colName.indexOf('#') >= 0)) {
       colName = "\"" + colName.toUpperCase() + "\"";
     }
 
     str = str + getIndent() + colName;
 
     if (column.isRowID())
     {
       str = str + " " + "VARCHAR";
     } else if (column.isInterval()) {
       if (column.getDataTypeString().toUpperCase().startsWith("INTERVAL YEAR"))
         str = str + " " + "INTERVAL YEAR TO MONTH";
       else
         str = str + " " + "INTERVAL DAY TO SECOND";
     }
     else {
       str = str + " " + getOraCompatibleType(column.getDataTypeString(), column.getLength());
     }
 
     if (((column.getDataType() == Column.DataType.VARCHAR) || (column.getDataType() == Column.DataType.NVARCHAR)) && (column.getLength() > 0))
     {
       str = str + "(" + column.getLength() + ")";
     }
     if (((column.getDataType() == Column.DataType.INTEGER) && (!column.getDataTypeString().equalsIgnoreCase("INTEGER")) && (!column.isSmallint()) && (!column.isBigInt())) || ((column.getDataType() == Column.DataType.NUMERIC) && (!column.isReal())))
     {
       if (column.getPrecision() > 0) {
         str = str + "(" + column.getPrecision();
         if (column.getScale() > 0)
           str = str + "," + column.getScale();
         str = str + ")";
       } else if (column.getDataTypeString().equalsIgnoreCase("NUMERIC"))
       {
         str = str + "(38, 8)";
       }
 
     }
 
     if ((!MigrationToolkit.skipColDefaultClause()) && (column.getDefaultClause() != null)) {
       str = str + " DEFAULT " + column.getDefaultClause();
     }
 
     if (!column.isNullable()) {
       str = str + " NOT NULL";
     }
     return str;
   }
 
   public String getCreateScript(Procedure proc) {
     String sql = "CREATE " + proc.getSQL();
     sql = sql + "\n";
 
     return sql;
   }
 
   public String getCreateScript(Function func) {
     String sql = "";
     return sql;
   }
 
   public String getCreateScript(Synonym syn) {
     String sql = "";
     return sql;
   }
 
   public String getCreateScript(Trigger trigger) {
     String sql = "";
     return sql;
   }
 
   public String getCreateScript(Constraint constraint) {
     String sql = "";
 
     String constTableName = constraint.getTableName();
 
     String constName = constraint.getName(true);
     if (constName.startsWith("_")) {
       constName = "\"" + constName + "\"";
     }
 
     if ((constraint.getType() == 'P') || (constraint.getType() == 'p')) {
       sql = sql + "ALTER TABLE " + constTableName + " ADD CONSTRAINT " + constName + " PRIMARY KEY (";
 
       for (int i = 0; i < constraint.getColumns().size(); i++) {
         sql = sql + constraint.getColumns().get(i).getName();
         if (i < constraint.getColumns().size() - 1)
           sql = sql + ", ";
       }
       sql = sql + ")\n";
     } else if ((constraint.getType() == 'U') || (constraint.getType() == 'u')) {
       sql = sql + "ALTER TABLE " + constTableName + " ADD CONSTRAINT " + constName + " UNIQUE (";
 
       for (int i = 0; i < constraint.getColumns().size(); i++) {
         sql = sql + constraint.getColumns().get(i).getName();
         if (i < constraint.getColumns().size() - 1)
           sql = sql + ", ";
       }
       sql = sql + ")\n";
     } else if ((constraint.getType() == 'C') || (constraint.getType() == 'c'))
     {
       if (!constraint.getSearchCondition().endsWith("IS NOT NULL"))
       {
         String searchCondition = constraint.getSearchCondition();
         String patternStr = "::([a-zA-Z]+)";
         searchCondition = searchCondition.replaceAll(patternStr, "");
 
         sql = sql + "ALTER TABLE " + constTableName + " ADD CONSTRAINT " + constraint.getName() + " CHECK (" + searchCondition;
 
         sql = sql + ")\n";
       }
 
     }
 
     return sql;
   }
 
   public String getCreateScript(Index index) {
     String sql = "CREATE";
 
     String indexTableName = index.getTableName();
     if ((Utility.isCaseSensitive(indexTableName, MTKConstants.DATABASE_TYPE.ENTERPRISEDB)) || (indexTableName.startsWith("_"))) {
       indexTableName = "\"" + indexTableName + "\"";
     }
 
     String indexName = index.getName();
     if ((Utility.isCaseSensitive(indexName, MTKConstants.DATABASE_TYPE.ENTERPRISEDB)) || (indexName.startsWith("_"))) {
       indexName = "\"" + indexName + "\"";
     }
 
     if (index.isUnique()) {
       sql = sql + " UNIQUE";
     }
     sql = sql + " INDEX " + indexName + " ON " + indexTableName + " (";
 
     if (index.hasExpression()) {
       sql = sql + convertToORaCompatibleExpr(index.getExpression());
     }
     else {
       for (int i = 0; i < index.getColumns().size(); i++) {
         sql = sql + index.getColumns().get(i).getName();
 
         if (i < index.getColumns().size() - 1) {
           sql = sql + ", ";
         }
       }
     }
 
     sql = sql + ")\n";
 
     return sql;
   }
 
   private String convertToORaCompatibleExpr(String expression) {
     StringBuilder expr = new StringBuilder();
     Hashtable truncDateFormatEDB_to_ORA = new Hashtable();
     truncDateFormatEDB_to_ORA.put("microsecond", "'MI'");
     truncDateFormatEDB_to_ORA.put("millisecond", "'MI'");
     truncDateFormatEDB_to_ORA.put("second", "'MI'");
     truncDateFormatEDB_to_ORA.put("minute", "'MI'");
     truncDateFormatEDB_to_ORA.put("hour", "'HH'");
     truncDateFormatEDB_to_ORA.put("day", "'DD'");
     truncDateFormatEDB_to_ORA.put("week", "'DD'");
     truncDateFormatEDB_to_ORA.put("month", "'MONTH'");
     truncDateFormatEDB_to_ORA.put("quarter", "'Q'");
     truncDateFormatEDB_to_ORA.put("year", "'YEAR'");
     truncDateFormatEDB_to_ORA.put("decade", "'YEAR'");
     truncDateFormatEDB_to_ORA.put("centuray", "'YEAR'");
     truncDateFormatEDB_to_ORA.put("millennium", "'YEAR'");
     if (expression.startsWith("date_trunc")) {
       String[] tokens = expression.split(",");
       if ((tokens != null) && ((tokens.length == 2) || (tokens.length == 3))) {
         tokens[0] = tokens[0].replaceFirst("date_trunc", "");
         tokens[0] = tokens[0].replaceAll("\\(", "");
         tokens[0] = tokens[0].replaceAll("\\)", "");
         tokens[0] = tokens[0].replaceAll("::text", "");
         tokens[0] = tokens[0].replaceAll("'", "");
         tokens[(tokens.length - 1)] = tokens[(tokens.length - 1)].replaceAll("\\(", "");
         tokens[(tokens.length - 1)] = tokens[(tokens.length - 1)].replaceAll("\\)", "");
         expr.append("trunc(");
         expr.append(tokens[(tokens.length - 1)]).append(",");
         expr.append((String)truncDateFormatEDB_to_ORA.get(tokens[0]));
         expr.append(")");
         return expr.toString();
       }
     }
     return expression;
   }
 
   public String getCreateScriptPackageSpec(Package pack)
   {
     String sql = "";
     return sql;
   }
 
   public String getCreateScriptPackageBody(Package pack) {
     String sql = "";
     return sql;
   }
 
   private String getOraCompatibleType(String dataType, int dataLength)
   {
     Pattern tsPtrn = Pattern.compile("time(stamp)?\\s*([(]\\d+[)])?\\s*without\\s+time\\s*zone", 2);
     Matcher tsMatcher = tsPtrn.matcher(dataType);
 
     Pattern tswzPtrn = Pattern.compile("time(stamp)?\\s*([(]\\d+[)])?\\s*with\\s+time\\s*zone", 2);
     Matcher tswzMatcher = tswzPtrn.matcher(dataType);
 
     Pattern bitPtrn = Pattern.compile("bit\\s*(varying)?\\s*([(]\\d+[)])*", 2);
     Matcher bitMatcher = bitPtrn.matcher(dataType);
 
     if (dataType.equalsIgnoreCase("character varying"))
     {
       if (dataLength == 0) {
         return "CLOB";
       }
       return "VARCHAR2";
     }
     if ((dataType.equalsIgnoreCase("bpchar")) || (dataType.equalsIgnoreCase("character"))) {
       return "CHAR";
     }
 
     if (tsMatcher.matches()) {
       String lengthStr = tsMatcher.group(2);
 
       if ((lengthStr == null) || (lengthStr.trim().length() == 0)) {
         lengthStr = "(6)";
       }
 
       return "TIMESTAMP" + lengthStr;
     }if (dataType.equalsIgnoreCase("double precision"))
       return "FLOAT";
     if (dataType.equalsIgnoreCase("timestamp")) {
       return "TIMESTAMP(6)";
     }
 
     if (dataType.equalsIgnoreCase("TEXT"))
       return "CLOB";
     if ((dataType.equalsIgnoreCase("BYTEA")) || (dataType.equalsIgnoreCase("RAW")))
     {
       return "BLOB";
     }
 
     if (((dataType.equalsIgnoreCase("VARCHAR")) || (dataType.equalsIgnoreCase("VARCHAR2"))) && (dataLength == 0))
     {
       return "CLOB";
     }if (dataType.equalsIgnoreCase("BIGINT")) {
       return "INTEGER";
     }
 
     if (tswzMatcher.matches()) {
       String lengthStr = tswzMatcher.group(2);
 
       if ((lengthStr == null) || (lengthStr.trim().length() == 0)) {
         lengthStr = "";
       }
 
       return "TIMESTAMP" + lengthStr + " WITH TIME ZONE";
     }if (dataType.equalsIgnoreCase("NAME"))
       return "VARCHAR2(64)";
     if (bitMatcher.matches()) {
       String bitvaryingFlag = bitMatcher.group(1);
       String lengthStr = bitMatcher.group(2);
 
       if (bitvaryingFlag != null) {
         if ((lengthStr == null) || (lengthStr.trim().length() == 0)) {
           return "VARCHAR (4000)";
         }
         return "VARCHAR" + lengthStr;
       }
 
       if (lengthStr != null) {
         return "CHAR" + lengthStr;
       }
       return "CHAR";
     }
 
     return dataType;
   }
 
   protected String getColumnsComments(Table table)
   {
     String sql = "";
 
     for (int i = 0; i < table.getColumns().size(); i++) {
       if (table.getColumns().get(i).getComments() != null) {
         sql = sql + "COMMENT ON COLUMN " + table.getName() + "." + table.getColumns().get(i).getName() + " IS '" + table.getColumns().get(i).getComments().replaceAll("'", "''") + "'" + MetaData.SQL_STATEMENT_DELIMITER;
       }
 
     }
 
     return sql;
   }
 
   public String getCreateScript(Role role)
   {
     return "";
   }
 
   public String getCreateScript(User user)
   {
     return "";
   }
 
   public String getCreateScript(Grant grant)
   {
     return "";
   }
 
   public String getCreateScript(Rule rule)
   {
     throw new UnsupportedOperationException("Not supported yet.");
   }
 
   public String getCreateScript(Group group)
   {
     throw new UnsupportedOperationException("Not supported yet.");
   }
 
   public String getCreateScript(Domain domain)
   {
     throw new UnsupportedOperationException("Not supported yet.");
   }
 
   public String getCreateScript(DataType type)
   {
     throw new UnsupportedOperationException("Not supported yet.");
   }
 }

/* 

 * 
 */